ervu-journal-service

Сервис предназначен для получения данных журналирования

Для получения данных сервис использует БД Postgresql

Сервис имеет следующие точки входа для REST-запросов:

POST /journal/auth
POST /journal/import
GET /journal/import/detail/{importId}
POST /journal/import/success
POST /journal/import/errors
POST /journal/object
GET /journal/object/v1/{objectId}
GET /query
GET /query/{queryDefinitionId}
GET /query2
GET /query2/{queryDefinitionId}
POST /report/data/{queryDefinitionId}
POST /journal/events/v1

Виды запросов

Запросы по журналу аутентификации. Для постраничной выборки каждый запрос данных сопровождается агрегатным запросом по количеству записей

SELECT count(*) FROM auth_journal aj
[WHERE clause]
 
SELECT aj.* FROM auth_journal aj
[WHERE clause]
ORDER BY {aj.time_created | aj.event_type | aj.full_name | aj.time_created} {DESC|ASC}
OFFSET :offset LIMIT :limit

WHERE clause имеет следующий вид

AND aj.time_created >= CAST(:dateFrom AS TIMESTAMP)
AND aj.time_created <= CAST(:dateTo AS TIMESTAMP)
AND (aj.first_name ilike :searchString OR  aj.last_name ilike :searchString OR  aj.middle_name ilike :searchString OR  aj.vk_name ilike :searchString OR  aj.full_name ilike :searchString)
AND aj.first_name ilike :firstName
AND aj.last_name ilike :lastName
AND aj.middle_name ilike :middleName
AND aj.event_type = :event
AND aj.result = :status
AND aj.vk_name ilike :vkName
AND aj.vk_id = :domainId

Запросы по журналу импорта

SELECT t1.* FROM import_journal as t1
WHERE
 t1.import_id = :importId
 
foreach(importJournal) {
  SELECT t1.* FROM import_results AS t1
  WHERE
   t1.import_id = :importId
  ORDER BY t1.full_name, t1.created_date
  LIMIT :limit OFFSET :offset
}

Для постраничной выборки каждый запрос данных сопровождается агрегатным запросом по количеству записей

SELECT count(*) FROM import_journal ij
[WHERE clause]
 
SELECT ij.* FROM import_journal ij
[WHERE clause]
ORDER BY {ij.start_time | ij.status} {DESC|ASC}
OFFSET :offset LIMIT :limit

WHERE clause имеет вид

AND ij.start_time >= CAST(:dateStartFrom AS TIMESTAMP)
AND ij.start_time <= CAST(:dateStartTo AS TIMESTAMP)
AND ij.finish_time >= CAST(:dateEndFrom AS TIMESTAMP)
AND ij.finish_time <= CAST(:dateEndTo AS TIMESTAMP)
AND (ij.source ilike :searchString OR ij.extra_info->'userInfo'->>'fullName' ilike :searchString OR ij.extra_info->'userInfo'->>'nameVK' ilike :searchString OR ij.file_name ilike :searchString)
AND ij.source ilike :source
AND ij.status = :status
AND ij.extra_info->'userInfo'->>'fullName' ilike :initiator
AND ij.extra_info->'userInfo'->>'nameVK' ilike :nameVK
AND ij.file_name ilike :fileName

Запросы по журналу импорта результатов

SELECT count(*) FROM import_results ir
left join import_journal ij on ir.import_id = ij.import_id
left join journal j on ij.journal_id = j.id
[WHERE clause]
 
SELECT ir.* FROM import_results ir 
left join import_journal ij on ir.import_id = ij.import_id
left join journal j on ij.journal_id = j.id
[WHERE clause]
ORDER BY {ir.created_date | ir.full_name} {DESC|ASC}
OFFSET :offset LIMIT :limit

WHERE clause имеет вид

AND ir.result in ('Импортирован', 'Обновлён') | AND (ir.result is null or ir.result NOT in ('Импортирован', 'Обновлён'))
AND ir.import_id= :import_id
AND ir.created_date >= CAST(:dateFrom AS TIMESTAMP)
AND ir.created_date <= CAST(:dateTo AS TIMESTAMP)
AND (ij.source ilike :searchString OR ir.full_name ilike :searchString OR ir.id_ern ilike :searchString OR j.first_name ilike :searchString OR j.last_name ilike :searchString OR j.middle_name ilike :searchString OR ij.extra_info->'userInfo'->>'fullName' ilike :searchString
     OR ij.extra_info->'userInfo'->>'nameVK' ilike :searchString OR ij.file_name ilike :searchString)
AND (ir.full_name ilike :firstName OR ij.extra_info->'userInfo'->>'fullName' ilike :firstName OR j.first_name ilike :firstName)
AND (ir.full_name ilike :lastName OR ij.extra_info->'userInfo'->>'fullName' ilike :lastName OR j.last_name ilike :lastName)
AND (ir.full_name ilike :middleName OR ij.extra_info->'userInfo'->>'fullName' ilike :middleName OR j.middle_name ilike :middleName)
AND ij.source ilike :source
AND ij.status = :status
AND ir.result = :result
AND ij.extra_info->'userInfo'->>'fullName' ilike :initiator
AND ij.file_name ilike :fileName
AND ij.extra_info->'userInfo'->>'nameVK' ilike :nameVK

Запросы по журналу. Для постраничной выборки каждый запрос данных сопровождается агрегатным запросом по количеству записей

SELECT count(*) FROM journal as t1
 
SELECT t1.* FROM journal as t1
ORDER BY t1.time_created DESC
LIMIT :limit OFFSET :offset

Запрос по object_history

SELECT oh.*, j.* FROM object_history oh
LEFT JOIN journal j ON j.id = oh.journal_id
WHERE oh.object_id = :object_id

Запрос по ui_action_description

SELECT * FROM ui_action_description

ORDER BY button_code

Запросы по ui_action_journal

SELECT count(*) FROM ui_action_journal ui
LEFT JOIN ui_action_description d on ui.ui_action_description_id = d.id
WHERE ui.vk_name is distinct from 'Firma moda'
    AND ui.event != 'GATEWAY_REST'
 
SELECT ui.* FROM ui_action_journal ui
LEFT JOIN ui_action_description d on ui.ui_action_description_id = d.id
WHERE ui.vk_name is distinct from 'Firma moda'
    AND ui.event != 'GATEWAY_REST'
[Filter clause]
ORDER BY {ui.event_time | ui.user_id | ui.vk_id | ui.vk_name | ui.user_name | button_code} {DESC|ASC}
LIMIT :limit OFFSET :offset

Filter clause имеет вид

AND ui.event_time >= CAST(:dateFrom AS TIMESTAMP)
AND ui.event_time <= CAST(:dateTo AS TIMESTAMP)
AND (ui.user_name  ilike :searchString OR d.description ilike :searchString OR ui.extra_info->>'fio' ilike :searchString OR ui.vk_name ilike :searchString)
AND ui.user_name = :userName
AND ui.user_name ILIKE :userFio
AND ui.user_id = :userId
AND ui.event IN ('%s')
AND ui.button_code = :buttonCode
AND ui.position = :position
AND ui.vk_name = :vkName
AND ui.vk_id = :domainId

Дерево страниц

Page Not Available

Виды запросов